package cn.demo.Util;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Author weizhongli
 * @Date 2020/3/22
 * @Version v1.0
 * @Description
 */
public class ExcelUtil<T> {
    private Class<T> clazz;

    public  File exprot(List<T> list) throws Exception, IllegalAccessException {
        Workbook wb = new HSSFWorkbook();
        // һ��sheetҳ�����־�������
        Sheet sheet = wb.createSheet(clazz.getSimpleName());
        Row titleRow = sheet.createRow(0);
        File file = null;
        CellStyle cellstyle = wb.createCellStyle();

        CreationHelper helper = wb.getCreationHelper();
        // ���õ�Ԫ�����������뷽ʽ
        cellstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // �������ڸ�ʽ
        cellstyle.setDataFormat(helper.createDataFormat().getFormat(
                "yyyy-MM-dd hh:mm:ss"));
        // ��ȡ���е������ֶ�
        Field[] files = clazz.getDeclaredFields();
        List<Class> clazz = new ArrayList<Class>();
        clazz.add(int.class);
        clazz.add(Integer.class);
        clazz.add(String.class);
        clazz.add(Date.class);


        for (int i = 0; i < files.length; i++) {
            if (!files[i].getType().getSimpleName().equals("Set")) {
                String name = files[i].getName();
                titleRow.createCell(i).setCellValue(name);
                for (int j = 0; j < list.size(); j++) {
                    Object obj = list.get(j);
                    Row row = sheet.createRow(j + 1);
                    for (int k = 0; k < files.length; k++) {
                        files[k].setAccessible(true);
                        if (clazz.contains(files[k].getType()) && files[k].get(obj) != null) {
                            System.out.println(files[k]);
                            String shuju = files[k].get(obj).toString();
                            row.createCell(k).setCellValue(shuju);
                        }
                    }
                }
            }
        }
        file = File.createTempFile("roleInfo", ".xls");
        FileOutputStream fos = new FileOutputStream(file);
        wb.write(fos);
        return file;
    }

    public List<T> imports(File file) throws Exception {
        InputStream is = new FileInputStream(file);
        POIFSFileSystem po1 = new POIFSFileSystem(is);
        Workbook wb = new HSSFWorkbook(po1);
        Sheet sheet = wb.getSheetAt(0);
        List<T> list = new ArrayList<T>();
        int rows = sheet.getPhysicalNumberOfRows();
        Field[] fields = clazz.getDeclaredFields();
        if (rows > 1) {
            for (int i = 0; i < rows; i++) {
                T t = clazz.newInstance();
                Row row = sheet.getRow(i);
                int cells = row.getPhysicalNumberOfCells();
                for (int j = 0; j < cells; j++) {
                    Object value = this.getCellValue(row.getCell(j));
                    if (!(fields[j].getType().getSimpleName().equals("Set"))) {
                        org.apache.commons.beanutils.BeanUtils.copyProperty(t, fields[j].getName(), value);
                    }
                }
                list.add(t);
            }
        }
        return list;

    }

    private Object getCellValue(Cell cell) {
        Object result = null;
        if (cell != null) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    result = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //�����ڽ����жϺͽ���
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        double cellValue = cell.getNumericCellValue();
                        result = HSSFDateUtil.getJavaDate(cellValue);
                    }
                    break;
                //��boolean���͵�ֵ���н���
                case Cell.CELL_TYPE_BOOLEAN:
                    result = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    result = cell.getCellFormula();
                    break;
                case Cell.CELL_TYPE_ERROR:
                    result = cell.getErrorCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    break;
                default:
                    break;
            }
        }
        return result;
    }

    public Class<T> getClazz() {
        return clazz;
    }

    public void setClazz(Class<T> clazz) {
        this.clazz = clazz;
    }

}
